/*
 * ClassAttrTable.java - SQL operations with the table 'class_attr'
 *  in the database 'context' (copy of WebDeso).
 *
 * Copyright (c) 2007 Computer-Aided Integrated Systems Laboratory, 
 * St. Petersburg Institute for Informatics and Automation RAS http://cais.lisa.iias.spb.su
 */

package dbcontext;

import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.SQLException;

/** Routines to work with the table 'class_attr' in the database 'context'.
 *  See Ontology.ClassAttr
 */
public class ClassAttrTable extends RelationTable {
    
    /** class of pair class-attribute in ontology */
    public ClassTable _class;
    
    /** attribute of pair class-attribute in ontology */
    public AttrTable  attr;
    
            
    /** Creates a new instance of ClassAttrTable */
    public ClassAttrTable() {
    }
    
    
    /** Inserts element to the table 'class' in MySQL database.
     * 
     * SQL example:
     * INSERT INTO class_attr (class_id,attr_id) VALUES(41,31);<br>
     *
     *  @param class_id     id of class (stored in the table 'class')
     *  @param attr_id      id of attribute (stored in the table 'attribute')
     */
    public static void insert ( java.sql.Connection conn,
                                int class_id, int attr_id) {
        Statement   s = null;
        ResultSet   rs= null;
        String      str_sql = null;
        
        ClassTable c = ClassTable.getByClassID(conn, class_id);
        AttrTable  a = AttrTable. getByAttrID (conn, attr_id);
        if(null == c || null == a)
            return;
        
        ClassAttrTable ca = get(conn, class_id, attr_id);
        if (ca != null) 
            return;
        
        try 
        {
            s = conn.createStatement ();
            str_sql = "INSERT INTO class_attr (class_id,attr_id) "+ 
                            "VALUES (" + class_id + 
                            ","+ attr_id + ")";
            s.executeUpdate (str_sql);
            
        }catch(SQLException ex) {
            System.err.println("SQLException (context.ClassAttrTable.java insert()):: sql='" + str_sql + "' " + ex.getMessage());
        } finally {
            if (rs != null) {   try { rs.close(); } catch (SQLException sqlEx) { }  rs = null; }
            if (s != null)  {   try { s.close();  } catch (SQLException sqlEx) { }  s = null;  }
        }
    }
    
    
    /** Selects data from tables 'class' and 'attr' 
     * if there is pair class-attr in the table 'class_attr'.
     *
     *  SQL:
     *  SELECT id FROM class_attr WHERE class_id=3 AND attr_id=13;
     *  
     *  @return ClassAttrTable object with initialized fields: _class, attr, id
     */
    public static ClassAttrTable get (java.sql.Connection conn,int class_id,int attr_id) {
        
        ClassAttrTable ca = null;
        Statement   s = null;
        ResultSet   rs= null;
        String      str_sql = null;
        
        ClassTable c = ClassTable.getByClassID(conn, class_id);
        AttrTable  a = AttrTable. getByAttrID (conn, attr_id);
        if(null == c || null == a) {
            System.out.println("Error in context.ClassAttrTable.java get()):: class and/or attribute are null, class="+c+", a="+a);
            return null;
        }
        
        try {
            s = conn.createStatement ();
            str_sql = "SELECT id FROM class_attr WHERE class_id=" + class_id + " AND attr_id=" + attr_id;
            s.executeQuery (str_sql);
            
            rs = s.getResultSet ();
            if (rs.next ())
            {
                ca = new ClassAttrTable ();
                ca._class = c;
                ca. attr  = a;
                ca.id = rs.getInt("id");
            }
        } catch(SQLException ex) {
            System.err.println("SQLException (Context.ClassAttrTable.java get()):: sql='" + str_sql + "' " + ex.getMessage());
        } finally {
            if (rs != null) {   try { rs.close(); } catch (SQLException sqlEx) { }  rs = null; }
            if (s != null)  {   try { s.close();  } catch (SQLException sqlEx) { }  s = null;  }
        }    
        return ca;
    }
    
    
    /** Selects data from tables 'class' and 'attr' 
     * if there is pair class-attr in the table 'class_attr'.
     *
     *  SQL:
     *  SELECT class_id,attr_id FROM class_attr WHERE id=3;
     *  
     *  @return ClassAttrTable object with initialized fields: _class, attr, id
     */
    public static ClassAttrTable getByID (java.sql.Connection conn,int id) {
        
        ClassAttrTable ca = null;
        Statement   s = null;
        ResultSet   rs= null;
        String      str_sql = null;
        
        try {
            s = conn.createStatement ();
            str_sql = "SELECT class_id,attr_id FROM class_attr WHERE id=" + id;
            s.executeQuery (str_sql);
            
            rs = s.getResultSet ();
            if (rs.next ())
            {
                int class_id = rs.getInt("class_id");
                int attr_id  = rs.getInt("attr_id");
                
                ClassTable c = ClassTable.getByClassID(conn, class_id);
                AttrTable  a = AttrTable. getByAttrID (conn, attr_id);
                if(null == c || null == a) {
                    System.out.println("Error in context.ClassAttrTable.java getByID()):: class and/or attribute are null, id="+id+
                                        ", class="+c+", a="+a);
                } else {
                    ca = new ClassAttrTable ();
                    ca._class = c;
                    ca. attr  = a;
                    ca.id    = id;
                }
            }
        } catch(SQLException ex) {
            System.err.println("SQLException (Context.ClassAttrTable.java getByID()):: sql='" + str_sql + "' " + ex.getMessage());
        } finally {
            if (rs != null) {   try { rs.close(); } catch (SQLException sqlEx) { }  rs = null; }
            if (s  != null) {   try { s.close();  } catch (SQLException sqlEx) { }  s  = null; }
        }    
        return ca;
    }
    
    
    /** Deletes attr's row by attribute name and ontolody ID.
     *  SQL:        
     *  DELETE FROM class_attr WHERE class_id=41;
     */
    public static void deleteByClassID (java.sql.Connection conn,int class_id) {
        
        Statement   s = null;
        ResultSet   rs= null;
        String      str_sql = null;
        
        try {
            s = conn.createStatement ();
            str_sql = "DELETE FROM class_attr WHERE class_id="+class_id;
            s.execute(str_sql);
            
        } catch(SQLException ex) {
            System.err.println("SQLException (Context.ClassAttrTable.java deleteByClassID()):: sql='" + str_sql + "' " + ex.getMessage());
        } finally {
            if (rs != null) {   try { rs.close(); } catch (SQLException sqlEx) { }  rs = null; }
            if (s != null)  {   try { s.close();  } catch (SQLException sqlEx) { }  s = null;  }
        }
    }

    
    /** Deletes attr's row by attribute name and ontolody ID.
     *  SQL:        
     *  DELETE FROM class_attr WHERE attr_id=31;
     */
    public static void deleteByAttrID (java.sql.Connection conn,int attr_id) {
        
        Statement   s = null;
        ResultSet   rs= null;
        String      str_sql = null;
        
        try {
            s = conn.createStatement ();
            str_sql = "DELETE FROM class_attr WHERE attr_id="+attr_id;
            s.execute(str_sql);
            
        } catch(SQLException ex) {
            System.err.println("SQLException (Context.ClassAttrTable.java deleteByAttrID()):: sql='" + str_sql + "' " + ex.getMessage());
        } finally {
            if (rs != null) {   try { rs.close(); } catch (SQLException sqlEx) { }  rs = null; }
            if (s != null)  {   try { s.close();  } catch (SQLException sqlEx) { }  s = null;  }
        }
    }
}
